create or replace package sza_oie_pay_trace_pkg
is
 procedure main(p_report_header_id pls_integer);
  procedure obtain(p_report_header_id pls_integer,p_submited_by out varchar2,p_segment2 out varchar2);--,);
end sza_oie_pay_trace_pkg;
/
create or replace package body sza_oie_pay_trace_pkg
is
  /******************************
    @Author:Jarwang
    @Since: July-13-2011
    @Purpose:
  ******************************/
  
  procedure main(p_report_header_id pls_integer)
  is
   pragma autonomous_transaction;
  begin
     update sza.sza_pay_header_all_log h
     set h.submited_date=sysdate,h.submited_by=fnd_global.USER_ID
     where h.report_header_id=p_report_header_id;
     if sql%rowcount =0 then
        insert into sza.sza_pay_header_all_log
          (submited_date, submited_by, report_header_id)
        values
          (sysdate,fnd_global.USER_ID,p_report_header_id);
     end if;
     
     update sza.sza_bank_payment_t t set t.status = 'I',t.record_seq = SZA.SZA_PAYMENT_REQUEST_S.nextval
      where t.report_header_id = p_report_header_id
        and t.status in('N','E');
        
     commit;
  exception
    when others then
      rollback;
      null;
  end main;
  
 procedure obtain(p_report_header_id pls_integer,p_submited_by out varchar2,p_segment2 out varchar2)
 is 
     cursor_name       INTEGER; 
     row_process       INTEGER; 
     l_submited_date    date;
     l_submited_by      varchar2(30);
     l_segment2         varchar2(140);
     l_sql              varchar2(200);
  begin
     l_sql :='select lg.submited_date,fu.user_name   from sza.sza_pay_header_all_log lg,fnd_user fu  where fu.user_id=lg.submited_by and lg.report_header_id=:header_id';
     cursor_name := dbms_sql.open_cursor; 
     DBMS_SQL.PARSE(cursor_name, 
          l_sql, 
          DBMS_SQL.NATIVE); 
     DBMS_SQL.bind_variable(cursor_name, 'header_id', p_report_header_id);
     DBMS_SQL.define_column(cursor_name, 1, l_submited_date);
     DBMS_SQL.define_column(cursor_name, 2, l_submited_by,30);
   
     row_process := dbms_sql.execute(cursor_name);

     loop
      if dbms_sql.fetch_rows(cursor_name) > 0 then
        dbms_sql.column_value(cursor_name, 1, l_submited_date);
        dbms_sql.column_value(cursor_name, 2, l_submited_by);
      else
        exit;
      end if;
    end loop;
    --p_submited_date:=l_submited_date;
    p_submited_by:=l_submited_by;
    dbms_sql.close_cursor(cursor_name);
    
    begin
     select apps.gl_flexfields_pkg.get_description_sql(50368,
                                                      2, 
                                                      h.flex_concatenated) into l_segment2
        from ap.ap_expense_report_headers_all h
       where h.report_header_id=p_report_header_id;
      exception
        when no_data_found then
         null;
    end; 
    
    -- Add by michael.lam , 20110725 - Start
    If length(l_segment2) > 90 Then
      l_segment2 := substr(l_segment2,1,9) || '*';
      Else
        l_segment2 := l_segment2;
    End If;
    -- Add by michael.lam , 20110725 - End

    p_segment2 :=l_segment2;
    EXCEPTION
      when others then
        null;  
  end obtain; 
  
end sza_oie_pay_trace_pkg;
/
